﻿using System;

using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ESRI.ArcGIS.DataSourcesFile;
using ESRI.ArcGIS.Display;
using ESRI.ArcGIS.Editor;
using ESRI.ArcGIS.EditorExt;
using ESRI.ArcGIS.esriSystem;
using ESRI.ArcGIS.Geodatabase;
using ESRI.ArcGIS.Geometry;
using ESRI.ArcGIS.GlobeCore;
using ESRI.ArcGIS.NetworkAnalysis;
using ESRI.ArcGIS.Output;
using ESRI.ArcGIS.SystemUI;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.Data;
using DIWAGIS.Modules;
using Microsoft.Office;
using DIWAGIS.Class;
using System.Reflection;
using System.Drawing;

namespace DIWAGIS.ApplicationLayer.ThongKeTaiSan
{
    public partial class frmThongKeDuongOng : Form
    {
        string sTableName = generalVariable.database + "." + generalVariable.schema + "." + ClassVariables.wOngChinh;
        Boolean isLoadComBox = false;
        Boolean kt = false;
        long sNResuilt = 0;
        public frmThongKeDuongOng()
        {
            InitializeComponent();
        }

        private void frmThongKeDuongOng_Load(object sender, EventArgs e)
        {
            progressBarTienTrinh.Visible = false; 
            DIWAGIS.Module.WorkingWithDomain cbx = new DIWAGIS.Module.WorkingWithDomain();
            cbx.loadDataDomainToCombobox(comboBoxNuocSanXuatOngChinh, DIWAGIS.Modules.generalVariable.ICurrentWorkspace, "DMNuocSanXuatOngNuoc");
            cbx.loadDataDomainToCombobox(comboBoxVatLieu, DIWAGIS.Modules.generalVariable.ICurrentWorkspace, "DMChatLieuOngChinh");
            cbx.LoadSubTypeToCombobox(comboBoxCoOng, sTableName, "CoOng", DIWAGIS.Modules.generalVariable.ICurrentWorkspace);
            SqlCommand sqlcommand = new SqlCommand();

            sqlcommand.CommandText = "SELECT TENHUYEN,TENHUYEN FROM HUYEN ";
            cbx.loadDataToCombobox(comboBoxQuanHuyen, sqlcommand);

            sqlcommand.CommandText = "SELECT TENCONDUONG,TENCONDUONG FROM " + generalVariable.database + "." + generalVariable.schema + "." + " CONDUONG ";
            cbx.loadDataToCombobox(comboBoxTuyenDuong, sqlcommand);

            isLoadComBox = true;
        }

        private void comboBoxQuanHuyen_SelectedIndexChanged(object sender, EventArgs e)
        {
              if (isLoadComBox == true)
            {
                string strWhere = "";
                if (!string.IsNullOrEmpty(comboBoxQuanHuyen.Text))
                {
                    strWhere = comboBoxQuanHuyen.SelectedValue.ToString();
                    SqlCommand sqlcommand = new SqlCommand();
                    DIWAGIS.Module.WorkingWithDomain cbx = new DIWAGIS.Module.WorkingWithDomain();
                    //sqlcommand = new System.Data.SqlClient.SqlCommand();
                    sqlcommand.CommandText = "SELECT TenHanhChinh,TenHanhChinh FROM " + generalVariable.database + "." + generalVariable.schema + "." + "HANHCHINH" + " WHERE TenHuyen = @TenHuyen";
                    sqlcommand.Parameters.AddWithValue("@TenHuyen", strWhere);
                    cbx.loadDataToCombobox(comboBoxPhuongXa, sqlcommand);

                    //loadDataToCombobox(cbxMaPhuong, sqlcommand);

                }
            }
        }

        private void buttonTimKiem_Click(object sender, EventArgs e)
        {
            IFeatureWorkspace pFWS = DIWAGIS.Modules.generalVariable.ICurrentWorkspace as IFeatureWorkspace;

            string strWhere = "";


            if (txtMaDMA.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND MaDMA ='" + (txtMaDMA.Text) + "'";
                }
                else
                {
                    strWhere = "MaDMA ='" + (txtMaDMA.Text) + "'";
                }
            }
            
            if (comboBoxNuocSanXuatOngChinh.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND NuocSanXuatOngChinh = " + (comboBoxNuocSanXuatOngChinh.SelectedValue);
                }
                else
                {
                    strWhere = "NuocSanXuatOngChinh = " + (comboBoxNuocSanXuatOngChinh.SelectedValue);
                }
            }

            if (comboBoxVatLieu.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND ChatLieuOngChinh ='" + (comboBoxVatLieu.SelectedValue) + "'";
                }
                else
                {
                    strWhere = "ChatLieuOngChinh ='" + (comboBoxVatLieu.SelectedValue) + "'";
                }
            }

            if (comboBoxCoOng.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND CoOng = " + (comboBoxCoOng.SelectedValue);
                }
                else
                {
                    strWhere = "CoOng = " + (comboBoxCoOng.SelectedValue);
                }
            }

            if (comboBoxQuanHuyen.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND Huyen =N'" + (comboBoxQuanHuyen.SelectedValue) + "'";
                }
                else
                {
                    strWhere = "Huyen =N'" + (comboBoxQuanHuyen.SelectedValue) + "'";
                }
            }

            if (comboBoxTuyenDuong.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND TenDuong =N'" + (comboBoxTuyenDuong.SelectedValue) + "'";
                }
                else
                {
                    strWhere = "TenDuong =N'" + (comboBoxTuyenDuong.SelectedValue) + "'";
                }
            }

            if (comboBoxPhuongXa.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND Phuong =N'" + (comboBoxPhuongXa.SelectedValue) + "'";
                }
                else
                {
                    strWhere = "Phuong =N'" + (comboBoxPhuongXa.SelectedValue) + "'";
                }
            }

            // ITable pTable = pFWS.OpenTable("gis_water.DBO.fcDongHoKhachHang") as ITable;

            IQueryDef queryDef = pFWS.CreateQueryDef();

            queryDef.Tables = sTableName;

            queryDef.SubFields = "CoOng,NuocSanXuatOngChinh,ChatLieuOngChinh,TenDuong,Phuong,Huyen,NgayLapDat,MaDMA,ChieuDai";

            queryDef.WhereClause = strWhere;
            DIWAGIS.Class.WorkingWithTable pC = new DIWAGIS.Class.WorkingWithTable();
            ITable pTable = pC.CreateQueryTable(DIWAGIS.Modules.generalVariable.ICurrentWorkspace, queryDef, sTableName);
            ICursor cur = pTable.Search(null, false);
            IRow curRow = null;
            double sChieuDai=0;
            //int fldIndex = pTable.Fields.FindField("ChieuDai");
            sNResuilt = pTable.RowCount(queryDef as IQueryFilter);
            int stateNameIndex = cur.Fields.FindField("ChieuDai");
            while ((curRow = cur.NextRow()) != null)
            {
                if ( !string.IsNullOrEmpty( curRow.get_Value(stateNameIndex).ToString()) )
                {
                    sChieuDai = sChieuDai + Convert.ToDouble(curRow.get_Value(stateNameIndex).ToString());
                    
                }
               
            }


            //sNResuilt = pTable.RowCount(queryDef as IQueryFilter);

            dataGridView1.Rows.Add(comboBoxCoOng.Text, comboBoxNuocSanXuatOngChinh.Text, comboBoxVatLieu.Text, comboBoxTuyenDuong.Text, comboBoxPhuongXa.Text, comboBoxQuanHuyen.Text, "", sChieuDai.ToString());
           


            // Assign a where clause to filter the results.
            //queryDef.WhereClause = strWhere;
            //DIWAGIS.Class.WorkingWithTable pC = new DIWAGIS.Class.WorkingWithTable();
            //ITable pTable = pC.CreateQueryTable(DIWAGIS.Modules.generalVariable.ICurrentWorkspace, queryDef, sTableName);
            
            //dataGridView1.Columns.Clear();           
            //TableWrapper m_tableWrapper = new TableWrapper(pTable);
            //m_tableWrapper.UseCVDomains = true;
            ////m_tableWrapper. = "IDDanhBo,LoaiCauTao,NguyenLyHD,NgayLapDat,SoNha,IDTuyenDuong,IDKVCN,IDPhuongXa";
            //bindingSourceOngChinh.DataSource = m_tableWrapper;
          
            //dataGridView1.DataSource = bindingSourceOngChinh;       
        }

        private void button2_Click(object sender, EventArgs e)
        {
            ExportDataGridViewToExcel();
        }
        private void ExportDataGridViewToExcel()
        {

            try
            {
                progressBarTienTrinh.Visible = true;

                Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

                // creating new WorkBook within Excel application
                Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

                // creating new Excelsheet in workbook
                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

                // see the excel sheet behind the program
                app.Visible = false;

                // get the reference of first sheet. By default its name is Sheet1.
                // store its reference to worksheet
                worksheet = workbook.Sheets["Sheet1"];
                worksheet = workbook.ActiveSheet;

                // changing the name of active sheet
                worksheet.Name = "Bao cao";

                // storing header part in Excel

                for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
                }

                // storing Each row and column value to excel sheet
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {

                    for (int j = 0; j < dataGridView1.Columns.Count - 1; j++)
                    {
                        if (dataGridView1.Rows[i].Cells[j].Value != null)
                            worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                        else
                            worksheet.Cells[i + 2, j + 1] = "";
                    }
                    Application.DoEvents();
                    if (kt = true)
                    {
                        break;
                    }
                }
                worksheet = null;

                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
            }
            catch
            {
                MessageBox.Show("Thông báo! Lỗi trong quá trình xuất báo cáo ", generalVariable.nameProgram, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            // save the application
            //workbook.SaveAs("D:\\output.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            // Exit from the application
            //app.Quit();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            kt = true;
        }

        private void comboBoxPhuongXa_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void comboBoxVatLieu_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

     

      
        }
    }

